<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 8.2.&nbsp; Managing the Database</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-1.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-3.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-8-SECT-2"></a>
<h3 id="title-IDAE2VJ" class="docSection1Title">8.2. Managing the Database</h3>
<a name="IDX-CHP-8-0360"></a> 
<a name="IDX-CHP-8-0361"></a> 
<a name="IDX-CHP-8-0362"></a> 
<a name="IDX-CHP-8-0363"></a> 

<p class="docText">Now that you're connected to the database, you can create users, databases, and tables. You may not need to create a database or user account if you're using a MySQL server in a hosted environment, and if they supplied you with a username and a database name.</p>
<a name="learnphpmysql-CHP-8-SECT-2.1"></a>
<h4 id="title-IDAD3VJ" class="docSection2Title">8.2.1. Creating Users</h4>
<p class="docText">To create users above and beyond the default privileged <tt>root</tt> user, issue the <tt>grant</tt> command. The <tt>grant</tt> command uses this syntax:</p>
<pre>
GRANT <tt><i>PRIVILEGES</i></tt> ON <tt><i>DATABASE.OBJECTS</i></tt> TO'<tt><i>USER</i></tt>'@'<tt><I>HOST</i></tt>' IDENTIFIED BY '<tt><i>PASSWORD</I></tt>';
</pre><BR>

<p class="docText">For example:</p>
<pre>
GRANT ALL PRIVILEGES ON *.* TO 'michele'@'localhost' IDENTIFIED BY 'secret';
</pre><BR>

<p class="docText">This creates the user <tt>michele</tt> who can access anything locally. To change to the <tt>michele</tt> user, at the <tt>mysql</tt> command prompt, type:</p>
<pre>
exit
</pre><br>

<p class="docText">Then start MySQL from the command line with the new username and password. The syntax for specifying the username and password when starting MySQL is:</p>
<pre>
mysql -h <tt><I>hostname</i></tt> -u <tt><i>username</I></tt> -p<tt><i>password</i></tt>
</pre><BR>

<p class="docText">If you don't want users to access tables other than their own, replace <tt>*</tt> with the name of the user's database, like this:</p>
<pre>
GRANT ALL PRIVILEGES ON `store`.* TO 'michele'@'localhost' IDENTIFIED BY 'secret';
</pre><br>

<p class="docText">You'll need to run the above line as <tt>root</tt> or as someone with permission. In the above code, the word <tt>store</tt> correlates to the database name to which privileges are assigned, which you'll create in the next section.</p>

<a name="learnphpmysql-CHP-8-SECT-2.2"></a>
<h4 id="title-IDAI5VJ" class="docSection2Title">8.2.2. Creating a MySQL Database</H4>
<p class="docText">You're going to create a database called <tt>store</tt>. The <tt>create database</tt> command works like this:</P>
<pre>
CREATE DATABASE `store`;
</pre><br>

<p class="docText">If this works, you'll get a result like this one:</p>
<pre>
Query OK, 1 row affected (0.03 sec)
</pre><br>

<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><TR><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><TR><td width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></TD><TD valign="top">
<p class="docText">Database names cannot contain any spaces. On Unix servers, such as Linux and Mac OS X, database names are also case sensitive.</P>
</td></tr></table></td></tr></table></p><br>
<p class="docText">To start using this database, type:</p>
<pre>
USE `store`;
</pre><br>

<p class="docText">You will get the result:</p>
<pre>
Database changed.
</pre><br>

<p class="docText">Assuming you've done everything correctly, you'll be set up with new data and selected it for use. Creating tables is an important concept, so that's where we're headed!</p>

<a name="learnphpmysql-CHP-8-SECT-2.3"></a>
<h4 id="title-IDAQAWJ" class="docSection2Title">8.2.3. Table Manipulation</h4>
<a name="IDX-CHP-8-0364"></a> 
<a name="IDX-CHP-8-0365"></a> 

<p class="docText">Once you've created a table and started storing information in it, you may find that you need to make a change to the column types. For example, you may find a field you thought would need only 30 characters actually needs 100. You could start all over and redefine the table, but you'd lose all your data. Thankfully, MySQL allows you to modify column types without losing your data.</p>
<a name="learnphpmysql-CHP-8-SECT-2.3.1"></a>
<H5 id="title-IDABBWJ" class="docSection3Title">8.2.3.1. Renaming a table</h5>
<p class="docText">To rename a table, use <tt>ALTER TABLE</tt> <tt><i>table</I></tt> <tt>RENAME</tt> <tt><I>newtable</i></tt>. In this example, we are renaming<a name="IDX-CHP-8-0366"></a> 
<a name="IDX-CHP-8-0367"></a> 
 the table from <tt>books</tt> to <tt>publications</tt>.</P>
<pre>
ALTER TABLE `books` RENAME `publications`;
</pre><br>

<p class="docText">This would look like <a class="docLink" href="#learnphpmysql-CHP-8-FIG-3">Figure 8-3</a>.</p>
<a name="learnphpmysql-CHP-8-FIG-3"></a><p><center>
<H5 class="docFigureTitle">Figure 8-3. Renaming a table</h5>
<img border="0" alt="" width="549" height="76" SRC="images/learnphpmysql_0803.jpg">
</center></p><BR>

<a name="learnphpmysql-CHP-8-SECT-2.3.2"></a>
<h5 id="title-IDAYCWJ" class="docSection3Title">8.2.3.2. Changing a column's data type</h5>
<p class="docText">To change a column data type,<a name="IDX-CHP-8-0368"></a> 
<a name="IDX-CHP-8-0369"></a> 
 use <tt>ALTER TABLE</tt> <tt><I>table</i></tt> <tt>MODIFY</tt> <tt><i>column datatype</i></tt>. The following syntax modifies the <tt>author</tt> field so that the column can take 150 characters.</p>
<pre>
ALTER TABLE `authors` MODIFY `author` VARCHAR(150);
</pre><BR>

<p class="docText">Changing a column's data type will look like <a class="docLink" href="#learnphpmysql-CHP-8-FIG-4">Figure 8-4</a>.</P>
<a name="learnphpmysql-CHP-8-FIG-4"></a><p><center>
<h5 class="docFigureTitle">Figure 8-4. Changing column's data type</h5>
<img border="0" alt="" width="549" height="95" SRC="images/learnphpmysql_0804.jpg">
</center></p><BR>

<a name="learnphpmysql-CHP-8-SECT-2.3.3"></a>
<h5 id="title-IDANEWJ" class="docSection3Title">8.2.3.3. Adding a column</H5>
<a name="IDX-CHP-8-0370"></a> 
<a name="IDX-CHP-8-0371"></a> 
<a name="IDX-CHP-8-0372"></a> 
<a name="IDX-CHP-8-0373"></a> 
<a name="IDX-CHP-8-0374"></a> 
<a name="IDX-CHP-8-0375"></a> 
<a name="IDX-CHP-8-0376"></a> 
<a name="IDX-CHP-8-0377"></a> 
<a name="IDX-CHP-8-0378"></a> 
<a name="IDX-CHP-8-0379"></a> 

<p class="docText">To add a column, use <tt>ALTER TABLE</tt> <tt><i>table</I></tt> <tt>ADD</tt> <tt><I>column datatype</I></tt>. Here, we're changing the <tt>publications</tt> table so a timestamp is automatically added to it.</p>
<pre>
ALTER TABLE publications ADD time TIMESTAMP;
</pre><br>

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-FIG-5">Figure 8-5</a> shows the result.</p>
<a name="learnphpmysql-CHP-8-FIG-5"></a><p><center>
<h5 class="docFigureTitle">Figure 8-5. Adding a column</h5>
<img border="0" alt="" width="549" height="76" SRC="images/learnphpmysql_0805.jpg">
</center></p><br>

<a name="learnphpmysql-CHP-8-SECT-2.3.4"></a>
<h5 id="title-IDAPHWJ" class="docSection3Title">8.2.3.4. Remove a column</h5>
<p class="docText">If you look at your database tables and decide you don't need a specific column, you can remove it. To remove a column, use <tt>ALTER TABLE</tt> <tt><i>table</i></tt> <tt>DROP</tt> <tt><i>column</i></tt>. Here, we're removing the <tt>pages</tt> column; therefore, we'll no longer know how many pages are in a book listed in the database.</P>
<pre>
ALTER TABLE publications DROP COLUMN pages;
</pre><br>

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-FIG-6">Figure 8-6</a> shows how it would look after you execute the command.</p>
<a name="learnphpmysql-CHP-8-FIG-6"></a><P><center>
<H5 class="docFigureTitle">Figure 8-6. Removing a column</h5>
<img border="0" alt="" width="549" height="76" SRC="images/learnphpmysql_0806.jpg">
</center></P><br>

<a name="learnphpmysql-CHP-8-SECT-2.3.5"></a>
<h5 id="title-IDA1IWJ" class="docSection3Title">8.2.3.5. Deleting an entire table</h5>
<p class="docText">Sometimes you may want to completely remove a table. Use the <tt>DROP</tt> command to permanently remove a table and its data.</P>
<pre>
DROP TABLE `authors`;
</pre><br>



<a name="learnphpmysql-CHP-8-SECT-2.4"></a>
<h4 id="title-IDAKJWJ" class="docSection2Title">8.2.4. Using phpMyAdmin</H4>
<a name="IDX-CHP-8-0380"></a> 
<a name="IDX-CHP-8-0381"></a> 

<p class="docText">The tool phpMyAdmin, available from <a class="docLink" target="_blank" href="http://www.phpmyadmin.net/">http://www.phpmyadmin.net/</a>, allows you to administer a MySQL database through your web browser. All that's required is a web server with PHP installed and a MySQL database to administer.</p>
<p class="docText">To install phpMyAdmin, follow these steps:</p>
<div style="font-weight:bold"><ol class="docList" type="1"><LI><div style="font-weight:normal"><p class="docList">Download the archive file, such as <span class="docEmphasis">phpMyAdmin-2.7.0-pl2.tar.gz</span> (Unix) or <span class="docEmphasis">phpMyAdmin-2.7.0-pl2.zip</span> (Windows).</p></div></li><li><div style="font-weight:normal"><p class="docList">Unpack the archive (including subdirectories) to a directory on your computer.</p></div></LI><LI><div style="font-weight:normal"><p class="docList">Transfer them to your ISP account where PHP files can be executed. Or, if you have a web server installed locally, transfer them to the document root.</p></div></li><li><div style="font-weight:normal"><p class="docList">Create the configuration file <span class="docEmphasis">config.inc.php</span> using a text editor. You can use the existing <span class="docEmphasis">config.default.php</span> file as an example. For versions of phpMyAdmin before 2.7, modify <span class="docEmphasis">config.inc.php</span> instead of creating a new file. You may modify this file before sending it to your web server to avoid having to use the editor that is native to the server.</p></div></LI><li><div style="font-weight:normal"><p class="docList">You will need to set the value of <tt>$cfg['PmaAbsoluteUri']</tt> to the URI location of where you are placing the files. For example, if you place the files in <span class="docEmphasis">www/phpmyadmin/</span> on your ISP, the URI value is <a class="docLink" target="_blank" href="http://www.isp_domain_name/phpmyadmin/">http://www.isp_domain_name/phpmyadmin/</a>.</P></div></li><LI><div style="font-weight:normal"><p class="docList">You also need to set the hostname of your database, the MySQL username, and the password.</P></div></LI><li><div style="font-weight:normal"><p class="docList">We recommend that you either limit access to this directory or set up a cookie or cookie authentication so that authorized users only can make changes to your database.</p></div></li><li><div style="font-weight:normal"><p class="docList">Browse to <a class="docLink" target="_blank" href="http://www.yourhost.com/myadmin_dir/index.php">http://www.yourhost.com/myadmin_dir/index.php</a>.</p></div></li></ol></div>
<p class="docText">Once installed and connected to the database, phpMyAdmin's main page looks similar to <a class="docLink" href="#learnphpmysql-CHP-8-FIG-7">Figure 8-7</a>, except in the case that you're running the stable release of 2.7.0-pl2, instead of the 2.6.2-pl1 release.</p>
<a name="learnphpmysql-CHP-8-FIG-7"></a><p><center>
<h5 class="docFigureTitle">Figure 8-7. Selecting a database to administer in phpMyAdmin</h5>
<img border="0" alt="" width="549" height="280" SRC="images/learnphpmysql_0807.jpg">
</center></p><br>
<p class="docText">You can select any configured databases from the drop-down list labeled Databases. The admin provides an easy way to see how your database is configured and what objects exist (such as tables), and you're even offered the option to add tables through the graphical interface. Using PHP admin, you can create new databases and tables, run queries, and display server statistics.</p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-FIG-8">Figure 8-8</a> shows the tables that are in the test database. Click on the <tt>authors</tt> table on the left to get more details on that table.</p>
<a name="learnphpmysql-CHP-8-FIG-8"></a><P><center>
<h5 class="docFigureTitle">Figure 8-8. The objects in the test database</h5>
<img border="0" alt="" width="549" height="275" SRC="images/learnphpmysql_0808.jpg">
</center></P><BR>
<p class="docText">In <a class="docLink" href="#learnphpmysql-CHP-8-FIG-9">Figure 8-9</a>, the table structure of the <tt>authors</tt> table is displayed. This screen provides an easy way to visualize the layout of a database, particularly if it's a database that you did not create yourself.</p>
<a name="learnphpmysql-CHP-8-FIG-9"></a><P><center>
<h5 class="docFigureTitle">Figure 8-9. Viewing the authors table structure in <pre><b>phpMyAdmin</b></pre></h5>
<img border="0" alt="" width="549" height="266" SRC="images/learnphpmysql_0809.jpg">
</center></P><br>
<p class="docText">To view the contents of a table, click on the Browse tab. <a class="docLink" href="#learnphpmysql-CHP-8-FIG-10">Figure 8-10</a> shows the Browse tab for the <tt>authors</tt> table.</p>
<a name="learnphpmysql-CHP-8-FIG-10"></a><P><center>
<h5 class="docFigureTitle">Figure 8-10. The data in the authors table as well as the query used to generate it</h5>
<img border="0" alt="" width="549" height="369" SRC="images/learnphpmysql_0810.jpg">
</center></P><br>
<p class="docText">The web-based administration tool provides an easy-to-use interface for both exploring your database and creating new objects or modifying data. You may find the graphical interface to be a refreshing change from the text-based command line of the <tt>mysql</tt> client.</p>
<p class="docText">Since you have MySQL up and running and have created a database, let's talk about backing up your databases. As you know, backing up your data is important. Between security, data integrity, and backups, you have the most crucial pieces of a database. We'll discuss security later in the book.</p>


<a href="11011536.html"><img src="images/pixel.jpg" alt="" width="1" height="1" border="0"></a></TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-1.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-3.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</html>
